package com.suning.sawp.service.impl.mainpush;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.annotation.Resource;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.suning.framework.dal.client.DalClient;
import com.suning.sawp.constants.LoggerConstants;
import com.suning.sawp.dto.common.RecordList;
import com.suning.sawp.intf.dao.task.TaskStoreAndClerkDaoService;
import com.suning.sawp.intf.mainpush.MainPushService;
import com.suning.sawp.intf.mainpush.OperationLogService;
import com.suning.sawp.intf.store.StoreInfoService;
import com.suning.sawp.po.mainpush.MainPushTask;
import com.suning.sawp.po.mainpush.OptLog;
import com.suning.sawp.po.mainpush.RegionCommodityTask;
import com.suning.sawp.po.mainpush.StoreCommodityTask;
import com.suning.sawp.po.mainpush.TaskRegion;
import com.suning.sawp.po.mainpush.TaskStore;
import com.suning.sawp.po.store.StoreInfo;
import com.suning.sawp.service.impl.task.BITaskService;
import com.suning.sawp.service.task.dto.BIABSaleCompletion;
import com.suning.sawp.service.task.dto.OrgABSaleCompletion;
import com.suning.sawp.service.util.DateUtils;

/**
 * 〈主推处理层〉<br>
 * 〈功能详细描述〉
 *
 * @author 12061748
 * @see [相关类/方法]（可选）
 * @since [产品/模块版本] （可选）
 */
@SuppressWarnings("deprecation")
@Service
public class MainPushServiceImpl implements MainPushService {

    private static final String BLANK = "--";
    private static final String ERROR_MESSAGE = "errorMessage";
    private static final String SEMICOLON = ";";
    private static final String RET_FLAG = "retFlag";
    private static final String SUCCESS = "1";
    private static final String FAIL = "0";
    private static final Logger LOGGER = LoggerFactory.getLogger(LoggerConstants.SAWP_SERVICE);
    @Autowired
    StoreInfoService storeInfoService;
    @Autowired
    OperationLogService operationLogService;
    @Autowired
    BITaskService bITaskService;
    @Resource
    DalClient dalClient;

    @Resource
    TaskStoreAndClerkDaoService taskStoreAndClerkDaoService;

    @Override
    public File storeAssignExport(String regionCode, String taskNo) {
        // 导出内容不包含已分配的数据
        List<Map<String, Object>> typeList = getRegionCommodityNameAndNumListExceptIsAssigned(taskNo);
        // 通过大区编码查询所有门店列表
        List<StoreInfo> stores = storeInfoService.queryStoresByRegionCode(regionCode);
        if (CollectionUtils.isEmpty(typeList) || CollectionUtils.isEmpty(stores)) {
            return null;
        }
        TaskRegion task = queryRegionTaskDetail(taskNo);
        // 组装创建各个大区的各个门店的销售目标excel
        return createStoreSaleTargetExl(typeList, stores, task);
    }

    void setDataValidation(HSSFSheet sh, HSSFCell cell) {
        // 单元格设置数据有效性
        DVConstraint constraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.INTEGER,
                DVConstraint.OperatorType.GREATER_OR_EQUAL, "0", null);
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(),
                cell.getColumnIndex(), cell.getColumnIndex());
        // 数据有效性对象
        HSSFDataValidation data_validation = new HSSFDataValidation(regions, constraint);
        // 加入sheet
        sh.addValidationData(data_validation);
    }

    private File createStoreSaleTargetExl(List<Map<String, Object>> typeList, List<StoreInfo> stores, TaskRegion task) {
        // 创建对Excel工作簿文件的引用
        HSSFWorkbook wb = new HSSFWorkbook();
        // 在Excel工作簿中建一工作表
        HSSFSheet sh = wb.createSheet("各门店销售目标");
        sh.protectSheet("passw0rd"); // 当前工作表为保护状态 密码为passw0rd
        // 设置单元格类型
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
        cellStyle.setWrapText(true);
        // 锁定单元格
        cellStyle.setLocked(true);

        // 需要用户填写的空格的样式，锁定取消
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
        style.setWrapText(true);
        style.setLocked(false);

        // 创建标题
        createTitle(typeList, sh, cellStyle, task);

        // 创建主标题（门店名称和商品编码集合）
        int rowNum = 6;
        for (int index = 0; index < stores.size(); index++) {
            Row row = sh.createRow(rowNum++);
            for (int cellnum = 0; cellnum < 2 + typeList.size(); cellnum++) {
                HSSFCell cell = (HSSFCell) row.createCell(cellnum);
                switch (cellnum) {
                    case 0:
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(stores.get(index).getStoreCode());
                        break;
                    case 1:
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(stores.get(index).getStoreName());
                        break;
                    default:
                        cell.setCellStyle(style);
                        setDataValidation(sh, cell);
                        break;
                }
            }
        }

        FileOutputStream out = null;
        try {
            final File createTempFile = File.createTempFile("temp", "temp.xls");
            out = new FileOutputStream(createTempFile);
            wb.write(out);
            return createTempFile;
        } catch (Exception e) {
            LOGGER.error("createStoreSaleTargetExl 生成导出文件失败.", e);
        } finally {
            if (null != out) {
                try {
                    out.close();
                } catch (IOException e) {
                    LOGGER.error("createStoreSaleTargetExl close FileOutputStream error.", e);
                }
            }
        }
        return null;
    }

    /**
     * 
     * 功能描述: 创建类型标题<br>
     * 〈功能详细描述〉
     *
     * @param typeMap
     * @param sh
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    private void createTitle(List<Map<String, Object>> typeList, HSSFSheet sh, CellStyle cellStyle, TaskRegion task) {
        StringBuilder titleLine0 = new StringBuilder(task.getOrgName()).append(SEMICOLON).append(task.getTaskTopic());
        StringBuilder titleLine1 = new StringBuilder(";商品型号;");
        StringBuilder titleLine2 = new StringBuilder(";商品归类编码;");
        StringBuilder titleLine3 = new StringBuilder(";主推类型;");
        StringBuilder titleLine4 = new StringBuilder(";商品编码集;");
        StringBuilder titleLine5 = new StringBuilder(";目标合计;");
        List<String> list = new ArrayList<String>();
        // 创建表头
        for (Map<String, Object> typeMap : typeList) {
            titleLine1.append(typeMap.get("commName")).append(SEMICOLON);
            titleLine2.append(typeMap.get("commClassifyCode")).append(SEMICOLON);
            titleLine3.append(typeMap.get("commType")).append(SEMICOLON);
            titleLine4.append(typeMap.get("commCodes")).append(SEMICOLON);
            titleLine5.append(typeMap.get("destNum")).append(SEMICOLON);
        }
        list.add(titleLine0.toString());
        list.add(titleLine1.toString());
        list.add(titleLine2.toString());
        list.add(titleLine3.toString());
        list.add(titleLine4.toString());
        list.add(titleLine5.toString());

        writeLine(sh, cellStyle, list);
    }

    private void writeLine(HSSFSheet sh, CellStyle cellStyle, List<String> list) {
        for (int i = 0; i < list.size(); i++) {
            // 定义表头
            String[] title = list.get(i).split(SEMICOLON);
            // 创建型号表头
            Row row = sh.createRow(i);
            for (int j = 0, len = title.length; j < len; j++) {
                HSSFCell cell = (HSSFCell) row.createCell(j);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(title[j]);
            }
        }
    }

    /**
     * 
     * 功能描述:检验导入文件格式和内容 <br>
     * 〈功能详细描述〉
     *
     * @param sh
     * @return
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    private Map<String, Object> validateExcel(Sheet sheet, String taskNo, String regionCode) {
        Map<String, Object> map = new HashMap<String, Object>();
        /**
         * 校验门店是否是该大区下面的
         */
        List<StoreInfo> stores = storeInfoService.queryStoresByRegionCode(regionCode);
        List<String> storeCodes = new ArrayList<String>();
        for (StoreInfo store : stores) {
            storeCodes.add(store.getStoreCode());
        }
        /**
         * 校验填写内容是否都是数字
         */
        int rows = sheet.getPhysicalNumberOfRows();
        for (int i = 6; i < rows; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                break;
            }
            int cells = row.getLastCellNum();
            for (int j = 0; j < cells; j++) {
                if (j == 0 && !storeCodes.contains(String.valueOf(getCellValue(row.getCell(j))))) {
                    map.put(RET_FLAG, FAIL);
                    map.put(ERROR_MESSAGE, "第" + (i + 1) + "行门店非该大区下，请检查门店");
                    return map;
                }
                // 模板解析对象，组装好所有列对象的归类
                if (j > 1 && StringUtils.isNotBlank(String.valueOf(getCellValue(row.getCell(j))))
                        && !isPositiveInteger(String.valueOf(getCellValue(row.getCell(j))))) {
                    map.put(RET_FLAG, FAIL);
                    map.put(ERROR_MESSAGE, "第" + (i + 1) + "行，第" + (j + 1) + "列目标台数录入非法，请检查");
                    return map;
                }
            }
        }
        /**
         * 查询出当前任务下分配状态为未分配的归类编码集，在判断导入的归类编码集在不在里面，有一个不在里面都失败
         */
        // 获取出当前任务下分配状态为未分配且未删除的商品的归类编码集
        List<String> ccfs = queryCommClassifyListByTaskNo(taskNo, MainPushTask.AssignStatusConstants.ASSIGNED,
                MainPushTask.CommodityStatusConstants.IS_DELETE);
        // 在获取导入的商品编码集
        // 组装门店商品归类码集合
        Row row2 = sheet.getRow(2);
        if (row2 != null) {
            int cells = row2.getLastCellNum();
            for (int j = 2; j < cells; j++) {
                String classifyCode = String.valueOf(getCellValue(row2.getCell(j)));
                if (!ccfs.contains(classifyCode)) {
                    map.put(RET_FLAG, FAIL);
                    map.put(ERROR_MESSAGE, "第3行，第" + (j + 1) + "列商品已分配或不属于当前任务，请检查");
                    return map;
                }
            }
        }
        // 校验商品类型A和B
        Row row3 = sheet.getRow(3);
        if (row3 != null) {
            int cells = row3.getLastCellNum();
            for (int j = 2; j < cells; j++) {
                String commType = String.valueOf(getCellValue(row3.getCell(j)));
                if (!"A".equals(commType) && !"B".equals(commType)) {
                    map.put(RET_FLAG, FAIL);
                    map.put(ERROR_MESSAGE, "第4行，第" + (j + 1) + "列商品类型非法，请检查");
                    return map;
                }
            }
        }

        // 组装门店商品任务集合
        Row row4 = sheet.getRow(4);
        if (row4 != null) {
            int cells = row4.getLastCellNum();
            for (int j = 2; j < cells; j++) {
                String commCodes = String.valueOf(getCellValue(row4.getCell(j)));
                int len = commCodes.split(",").length;
                if (len > 99) {
                    map.put(RET_FLAG, FAIL);
                    map.put(ERROR_MESSAGE, "第5行，第" + (j + 1) + "列商品编码超过99个限制，请检查");
                    return map;
                }
                for (String code : commCodes.split(",")) {
                    if (StringUtils.isBlank(code) || !isPositiveInteger(code)) {
                        map.put(RET_FLAG, FAIL);
                        map.put(ERROR_MESSAGE, "第5行，第" + (j + 1) + "列存在非数字商品编码，请检查");
                        return map;
                    }
                    if (code.length() != 9) {
                        map.put(RET_FLAG, FAIL);
                        map.put(ERROR_MESSAGE, "第5行，第" + (j + 1) + "列存在非9位商品编码，请检查");
                        return map;
                    }
                }

            }
        }

        // 校验各门店目标数目总和必须大于等于目标总计
        Map<Integer, Object> dataMap = new HashMap<Integer, Object>();

        Row row1 = sheet.getRow(1);
        if (row1 != null) {
            int cells = row1.getLastCellNum();
            for (int j = 2; j < cells; j++) {
                String commName = String.valueOf(getCellValue(row1.getCell(j)));
                StoreCommodityTask sct = new StoreCommodityTask();
                sct.setCommName(commName);
                dataMap.put(j - 2, sct);
            }
        }

        Row row5 = sheet.getRow(5);
        if (row5 != null) {
            int cells = row5.getLastCellNum();
            for (int j = 2; j < cells; j++) {
                Integer totalNum = Integer.valueOf(StringUtils.isBlank((String) getCellValue(row5.getCell(j))) ? "0"
                        : (String) getCellValue(row5.getCell(j)));
                StoreCommodityTask sct = (StoreCommodityTask) dataMap.get(j - 2);
                // 临时将总合计目标数存放在这个字段里面以便于后面和下面各个门店总数比较
                sct.setDestNum(totalNum);
            }
        }

        // 开始计算各个门店分配数量合计
        int rowsEx = sheet.getPhysicalNumberOfRows();
        for (int i = 6; i < rowsEx; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                break;
            }
            int cells = row.getLastCellNum();
            for (int j = 2; j < cells; j++) {
                Integer assignNum = Integer.valueOf(StringUtils.isBlank((String) getCellValue(row.getCell(j))) ? "0"
                        : (String) getCellValue(row.getCell(j)));
                StoreCommodityTask sct = (StoreCommodityTask) dataMap.get(j - 2);
                if (i == 6) {
                    sct.setCompltNum(assignNum);
                } else {
                    sct.setCompltNum(sct.getCompltNum() + assignNum);
                }
            }
        }

        // 依次比较目标数量总计是否小于分配数量之和
        for (int i = 0; i < dataMap.size(); i++) {
            StoreCommodityTask sct = (StoreCommodityTask) dataMap.get(i);
            if (sct.getCompltNum() < sct.getDestNum()) {
                map.put(RET_FLAG, FAIL);
                map.put(ERROR_MESSAGE, sct.getCommName() + "型号分配的合计目标台数" + sct.getCompltNum() + "未达到总目标台数"
                        + sct.getDestNum() + "，请重新分配后上传");
                return map;
            }
        }

        map.put(RET_FLAG, SUCCESS);
        return map;
    }

    /**
     * 
     * 功能描述:校验是否是正整数 <br>
     * 〈功能详细描述〉
     *
     * @param str
     * @return
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    private boolean isPositiveInteger(String str) {
        Matcher mer = Pattern.compile("^\\d+$").matcher(str);
        return mer.find();
    }

    @Override
    public List<Map<String, Object>> getRegionCommodityNameAndNumListExceptIsAssigned(String taskNo) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("taskNo", taskNo);
        // 排除已分配的
        paramMap.put("assignStatus", MainPushTask.AssignStatusConstants.ASSIGNED);
        // 排除已删除的
        paramMap.put("commStatus", MainPushTask.CommodityStatusConstants.IS_DELETE);
        return dalClient.queryForList("mainPush.getRegionCommodityTypeAndNumList", paramMap);
    }

    @Override
    public List<RegionCommodityTask> getRegionCommodityTaskList(String taskNo) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("taskNo", taskNo);
        return dalClient.queryForList("mainPush.getRegionCommodityTaskList", paramMap, RegionCommodityTask.class);
    }

    @Override
    public Map<String, Object> queryRegionMainPushList(String regionCode, String taskTopic, String startTime,
            String endTime, Integer start, Integer limit) {
        Map<String, Object> result = new HashMap<String, Object>();
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("regionCode", regionCode);
        if (StringUtils.isNotBlank(taskTopic)) {
            paramMap.put("taskTopic", "%" + taskTopic + "%");
        }
        paramMap.put("startTime", startTime);
        if (StringUtils.isNotBlank(endTime)) {
            paramMap.put("endTime", endTime + " 23:59:59");
        }
        paramMap.put("start", start);
        paramMap.put("limit", limit);
        List<TaskRegion> list = dalClient.queryForList("mainPush.queryRegionMainPushList", paramMap, TaskRegion.class);
        Map<String, Object> countMap = dalClient.queryForMap("mainPush.queryRegionMpTaskCount", paramMap);
        int count = ((Long) countMap.get("num")).intValue();
        result.put("data", new RecordList<TaskRegion>(list, count));
        result.put(RET_FLAG, SUCCESS);
        return result;
    }

    @Override
    public Map<String, Object> queryRegionTaskCommList(String taskNo, Integer start, Integer limit) {
        Map<String, Object> result = new HashMap<String, Object>();
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("taskNo", taskNo);
        paramMap.put("start", start);
        paramMap.put("limit", limit);
        List<RegionCommodityTask> list = dalClient.queryForList("mainPush.queryRegionTaskCommList", paramMap,
                RegionCommodityTask.class);
        Boolean isShowDownloadBut = false;
        for (RegionCommodityTask rct : list) {
            // 一旦出现未分配且商品状态不是已删除的记录，则前台显示模板下载按钮
            if (!MainPushTask.AssignStatusConstants.ASSIGNED.equals(rct.getAssignStatus())
                    && !MainPushTask.CommodityStatusConstants.IS_DELETE.equals(rct.getCommStatus())) {
                isShowDownloadBut = true;
                break;
            }
        }
        Map<String, Object> countMap = dalClient.queryForMap("mainPush.queryRegionTaskCommNum", paramMap);
        int count = ((Long) countMap.get("num")).intValue();
        result.put("data", new RecordList<RegionCommodityTask>(list, count));
        result.put("isShowDownloadBut", isShowDownloadBut);
        result.put(RET_FLAG, SUCCESS);
        return result;
    }

    @Override
    public Map<String, Object> queryStoreTaskCommList(String regionTaskNo, String commClassifyCode) {
        Map<String, Object> result = new HashMap<String, Object>();
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("taskNo", regionTaskNo);
        paramMap.put("commClassifyCode", commClassifyCode);
        List<StoreCommodityTask> list = dalClient.queryForList("mainPush.queryStoreTaskCommList", paramMap,
                StoreCommodityTask.class);
        result.put("data", list);
        result.put(RET_FLAG, SUCCESS);
        return result;
    }

    @Transactional
    @Override
    public Map<String, Object> storeAssignImport(Sheet sheet, String taskNo, String regionCode) {
        /**
         * 检验数据是否符合规范
         */
        Map<String, Object> result = validateExcel(sheet, taskNo, regionCode);
        if (MapUtils.isEmpty(result) || FAIL.equals(result.get(RET_FLAG))) {
            return result;
        }

        // 检验通过，开始对数据进行保存
        // 通过大区任务id查询任务详情信息
        TaskRegion task = queryRegionTaskDetail(taskNo);

        // 组装门店商品任务模板集合
        List<StoreCommodityTask> modelSctList = new ArrayList<StoreCommodityTask>();

        Row row0 = sheet.getRow(1);
        if (row0 != null) {
            int cells = row0.getLastCellNum();
            for (int j = 2; j < cells; j++) {
                // 模板解析对象，组装好所有列对象的归类
                StoreCommodityTask sct = new StoreCommodityTask();
                // 组装商品名称
                sct.setCommName(row0.getCell(j).getStringCellValue());
                modelSctList.add(sct);
            }
        }

        // 组装门店商品归类码集合
        Row row1 = sheet.getRow(2);
        if (row1 != null) {
            int cells = row1.getLastCellNum();
            for (int j = 2; j < cells; j++) {
                StoreCommodityTask sct = modelSctList.get(j - 2);
                // 门店编码
                sct.setCategoryCode(task.getCategoryCode());
                sct.setCommClassifyCode(Integer.valueOf((String) getCellValue(row1.getCell(j))));
                sct.setCompltNum(0);
                sct.setAssignStatus(MainPushTask.AssignStatusConstants.PRE_ASSIGN);
                sct.setCommStatus(MainPushTask.CommodityStatusConstants.NEW);
            }
        }

        // 组装门店商品任务集合
        Row row2 = sheet.getRow(3);
        if (row2 != null) {
            int cells = row2.getLastCellNum();
            for (int j = 2; j < cells; j++) {
                StoreCommodityTask sct = modelSctList.get(j - 2);
                // 门店编码
                sct.setCommType(String.valueOf(getCellValue(row2.getCell(j))));
            }
        }

        // 组装门店商品任务集合
        Row row3 = sheet.getRow(4);
        if (row3 != null) {
            int cells = row3.getLastCellNum();
            for (int j = 2; j < cells; j++) {
                StoreCommodityTask sct = modelSctList.get(j - 2);
                // 门店编码
                sct.setCommCodes(String.valueOf(getCellValue(row3.getCell(j))));
            }
        }

        // 组装大区下的门店集合,大区任务拆分成门店任务
        List<TaskStore> storeTaskList = new ArrayList<TaskStore>();
        int rows = sheet.getPhysicalNumberOfRows();
        for (int i = 6; i < rows; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            // 保存门店任务表
            long storeTaskNo = 0;
            // 通过大区任务编码和门店组织编码查询门店任务是否已经存在
            TaskStore taskStore = queryStoreTaskInfoByParTaskNo(taskNo, String.valueOf(getCellValue(row.getCell(0))));
            if (taskStore == null) {
                taskStore = new TaskStore();
                taskStore.setParTaskNo(taskNo);
                // 门店编码
                taskStore.setOrgCode(String.valueOf(getCellValue(row.getCell(0))));
                taskStore.setOrgName(String.valueOf(getCellValue(row.getCell(1))));
                taskStore.setCategoryCode(task.getCategoryCode());
                taskStore.setCategoryName(task.getCategoryName());
                taskStore.setStartTime(task.getStartTime());
                taskStore.setEndTime(task.getEndTime());
                taskStore.setTaskTopic(task.getTaskTopic());
                // 任务的分配状态需要改为未分配
                taskStore.setTaskStatus(task.getTaskStatus());
                taskStore.setAssignStatus(MainPushTask.AssignStatusConstants.PRE_ASSIGN);
                storeTaskNo = saveTaskStore(taskStore);
            } else {
                storeTaskNo = taskStore.getTaskNo();
            }

            storeTaskList.add(taskStore);

            // 解析列模板对象
            for (int j = 0; j < modelSctList.size(); j++) {
                StoreCommodityTask commTask = modelSctList.get(j);
                // 组装门店商品表的目标数量
                String destNum = StringUtils.isBlank((String) getCellValue(row.getCell(j + 2))) ? "0"
                        : String.valueOf(getCellValue(row.getCell(j + 2)));
                // 分配数量为0只保存任务，不保存商品表
                if (!"0".equals(destNum)) {
                    commTask.setDestNum(Integer.valueOf(destNum));
                    commTask.setTaskNo(storeTaskNo);
                    saveStoreCommTask(commTask);
                    // 通过分类编码更新大区商品表状态为已分配
                    // 修改大区商品表分配状态为已分配,商品状态为正常
                    updateRegionCommAssignStatusByClassifyCode(taskNo, commTask.getCommClassifyCode(),
                            MainPushTask.AssignStatusConstants.ASSIGNED,
                            MainPushTask.CommodityStatusConstants.NOT_DELETE);
                }
            }
        }
        // 判断当前任务下所有商品均已分配（查询不等于已分配且商品状态不为已删除的数量）
        long count = queryCommRegionTaskUnAssignedNum(taskNo, MainPushTask.AssignStatusConstants.ASSIGNED);
        if (count == 0) {
            // 修改大区任务表状态为已分配
            updateRegionTaskAssignStatusByTaskNo(taskNo, MainPushTask.AssignStatusConstants.ASSIGNED);
        }

        result.put(RET_FLAG, SUCCESS);
        result.put(ERROR_MESSAGE, "");
        return result;
    }

    private Object getCellValue(Cell cell) {
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_FORMULA:
                return "";
            case HSSFCell.CELL_TYPE_NUMERIC:
                // 将number类型的设置成为string类型, 默认取出字符类型内容
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                return cell.getStringCellValue();
            case HSSFCell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            default:
                return "";
        }
    }

    @Override
    public TaskRegion queryRegionTaskDetail(String taskNo) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("taskNo", taskNo);
        List<TaskRegion> list = dalClient.queryForList("mainPush.queryRegionMainPushList", paramMap, TaskRegion.class);
        if (CollectionUtils.isNotEmpty(list)) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public Long saveTaskStore(TaskStore task) {
        return (Long) dalClient.persist(task);
    }

    @Override
    public Long saveStoreCommTask(StoreCommodityTask sct) {
        return (Long) dalClient.persist(sct);
    }

    @Transactional
    @Override
    public Map<String, Object> upsertStoreCommTask(String taskNo, String commClassifyCode,
            List<StoreCommodityTask> storeList, String optStaffCode) {
        Map<String, Object> map = new HashMap<String, Object>();
        if (CollectionUtils.isEmpty(storeList)) {
            map.put(RET_FLAG, FAIL);
            map.put(ERROR_MESSAGE, "入参有误");
            return map;
        }
        // 查询大区商品某一型号的任务详情
        RegionCommodityTask taskDetail = getRegionCommodityTaskDetail(taskNo, commClassifyCode);
        if (taskDetail == null) {
            map.put(RET_FLAG, FAIL);
            map.put(ERROR_MESSAGE, "当前任务不存在");
            return map;
        }
        // 查询门店商品表中分类编码是commClassifyCode的记录
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("taskNo", taskNo);
        paramMap.put("commClassifyCode", commClassifyCode);
        List<StoreCommodityTask> slist = dalClient.queryForList("mainPush.queryStoreTaskCommList", paramMap,
                StoreCommodityTask.class);
        if (CollectionUtils.isEmpty(slist)) {
            map.put(RET_FLAG, FAIL);
            map.put(ERROR_MESSAGE, "门店商品任务不存在");
            return map;
        }

        Map<Integer, Object> mMap = new HashMap<Integer, Object>();
        for (StoreCommodityTask task : slist) {
            mMap.put(task.getId(), task.getDestNum());
        }

        int destNum = taskDetail.getDestNum();
        int storeDestSum = 0;
        for (StoreCommodityTask sct : storeList) {
            storeDestSum += sct.getDestNum();
        }
        if (destNum > storeDestSum) {
            map.put(RET_FLAG, FAIL);
            map.put(ERROR_MESSAGE, "分配的合计目标台数" + storeDestSum + "未达到总目标台数" + destNum);
            return map;
        }
        // 检验通过则更新数据
        for (StoreCommodityTask sct : storeList) {
            saveStoreCommInfo(taskDetail, sct, mMap, optStaffCode);
        }
        // 修改大区商品表分配状态为已分配,商品状态为正常
        updateRegionCommAssignStatusByClassifyCode(taskNo, Integer.valueOf(commClassifyCode),
                MainPushTask.AssignStatusConstants.ASSIGNED, MainPushTask.CommodityStatusConstants.NOT_DELETE);

        // 判断当前任务下所有商品均已分配（查询不等于已分配且商品状态不为已删除的数量）
        long count = queryCommRegionTaskUnAssignedNum(taskNo, MainPushTask.AssignStatusConstants.ASSIGNED);
        if (count == 0) {
            // 修改大区任务表状态为已分配
            updateRegionTaskAssignStatusByTaskNo(taskNo, MainPushTask.AssignStatusConstants.ASSIGNED);
        }
        map.put(RET_FLAG, SUCCESS);
        return map;
    }

    /**
     * 
     * 功能描述:保存更新门店商品表目标数目数据 <br>
     * 〈功能详细描述〉
     *
     * @param taskDetail
     * @param sct
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    private void saveStoreCommInfo(RegionCommodityTask taskDetail, StoreCommodityTask sct, Map<Integer, Object> mMap,
            String optStaffCode) {
        // 之前未分配的门店，门店商品表未存在记录
        if (sct.getId() == null) {
            Integer destNum = sct.getDestNum();
            Long taskNo = sct.getTaskNo();
            // 分配数量为0相当于不分配任务，不生成商品任务记录
            if (destNum == null || destNum == 0) {
                return;
            }
            try {
                BeanUtils.copyProperties(sct, taskDetail);
            } catch (IllegalAccessException e) {
                LOGGER.error("IllegalAccessException", e);
            } catch (InvocationTargetException e) {
                LOGGER.error("InvocationTargetException", e);
            }
            sct.setDestNum(destNum);
            sct.setId(null);
            sct.setAssignStatus(MainPushTask.AssignStatusConstants.PRE_ASSIGN);
            sct.setCommStatus(MainPushTask.CommodityStatusConstants.MODIFIED);
            sct.setTaskNo(taskNo);
            // 新增数据
            dalClient.persist(sct);
            OptLog log = new OptLog();
            log.setOptOrgLevel(MainPushTask.OrgLevelConstants.LEVEL_REGION);
            log.setOptType(OptLog.OptTypeConstants.TYPE_UPDATE);
            log.setOptStaffCode(optStaffCode);
            log.setTaskNo(taskDetail.getTaskNo());
            StringBuilder content = new StringBuilder("商品型号:").append(taskDetail.getCommName()).append(",分配门店:")
                    .append(sct.getOrgCode()).append(",分配数量:0→").append(sct.getDestNum());
            log.setOptContent(content.toString());
            operationLogService.log(log);
        } else {
            // 判断是否对原先的目标数量进行了修改
            if (!mMap.get(sct.getId()).equals(sct.getDestNum())) {
                sct.setAssignStatus(MainPushTask.AssignStatusConstants.MODITY_TOBE_ASSIGN);
                sct.setCommStatus(MainPushTask.CommodityStatusConstants.MODIFIED);
                // 通过id更新数目
                updateStoreCommodityDestNumById(sct);
                // 更新门店任务状态为修改待分配
                updateStoreTaskByTaskNo(sct.getTaskNo(), MainPushTask.AssignStatusConstants.MODITY_TOBE_ASSIGN);

                OptLog log = new OptLog();
                log.setOptOrgLevel(MainPushTask.OrgLevelConstants.LEVEL_REGION);
                log.setOptType(OptLog.OptTypeConstants.TYPE_UPDATE);
                log.setOptStaffCode(optStaffCode);
                log.setTaskNo(taskDetail.getTaskNo());
                StringBuilder content = new StringBuilder("商品型号:").append(taskDetail.getCommName()).append(",分配门店:")
                        .append(sct.getOrgCode()).append(",分配数量:").append(mMap.get(sct.getId())).append("→")
                        .append(sct.getDestNum());
                log.setOptContent(content.toString());
                operationLogService.log(log);
            }

        }
    }

    /**
     * 
     * 功能描述: <br>
     * 〈功能详细描述〉
     *
     * @param taskNo
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    private void updateStoreTaskByTaskNo(Long taskNo, String assignStatus) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("taskNo", taskNo);
        paramMap.put("assignStatus", assignStatus);
        dalClient.execute("mainPush.updateStoreTaskByTaskNo", paramMap);
    }

    @Override
    public RegionCommodityTask getRegionCommodityTaskDetail(String taskNo, String commClassifyCode) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("taskNo", taskNo);
        paramMap.put("commClassifyCode", commClassifyCode);
        List<RegionCommodityTask> list = dalClient.queryForList("mainPush.getRegionCommodityTaskDetail", paramMap,
                RegionCommodityTask.class);
        if (CollectionUtils.isNotEmpty(list)) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public TaskRegion queryRegionCommDestSum(TaskRegion regionTask) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("taskNo", regionTask.getTaskNo());
        Map<String, Object> map = dalClient.queryForMap("mainPush.queryRegionCommDestSum", paramMap);
        if (MapUtils.isNotEmpty(map)) {
            regionTask.setTotalNum(map.get("totalNum") == null ? "0" : String.valueOf(map.get("totalNum")));
            regionTask
                    .setCompltNum(map.get("compltTotalNum") == null ? "0" : String.valueOf(map.get("compltTotalNum")));
        }
        return regionTask;
    }

    @Override
    public void updateStoreCommodityDestNumById(StoreCommodityTask sct) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("id", sct.getId());
        paramMap.put("assignStatus", sct.getAssignStatus());
        paramMap.put("commStatus", sct.getCommStatus());
        paramMap.put("destNum", sct.getDestNum());
        dalClient.execute("mainPush.updateStoreCommodityDestNumById", paramMap);
    }

    @Override
    public void updateRegionTaskAssignStatusByTaskNo(String taskNo, String assignStatus) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("taskNo", taskNo);
        paramMap.put("assignStatus", assignStatus);
        dalClient.execute("mainPush.updateRegionTaskAssignStatusByTaskNo", paramMap);

    }

    @Override
    public void updateRegionCommAssignStatusByClassifyCode(String taskNo, Integer classifyCode, String assignStatus,
            String commStatus) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("classifyCode", classifyCode);
        paramMap.put("taskNo", taskNo);
        paramMap.put("assignStatus", assignStatus);
        paramMap.put("commStatus", commStatus);
        dalClient.execute("mainPush.updateRegionCommAssignStatusByClassifyCode", paramMap);
    }

    @Override
    public Map<String, Object> queryOperationLog(String taskNo, String startTime, String endTime, Integer start,
            Integer limit) {
        Map<String, Object> result = new HashMap<String, Object>();
        List<OptLog> list = operationLogService.queryOperationLog(taskNo, startTime, endTime, start, limit);
        int count = operationLogService.queryOptLogCount(taskNo, startTime, endTime);
        result.put("data", new RecordList<OptLog>(list, count));
        result.put(RET_FLAG, SUCCESS);
        return result;
    }

    @Override
    public File exportCompltInfo(List<String> taskNos, Boolean isHq) {
        // 创建对Excel工作簿文件的引用
        HSSFWorkbook wb = new HSSFWorkbook();
        for (String taskNo : taskNos) {
            createSheetByTaskNo(wb, taskNo, isHq);
        }
        FileOutputStream out = null;
        try {
            final File createTempFile = File.createTempFile("temp", "temp.xls");
            out = new FileOutputStream(createTempFile);
            wb.write(out);
            return createTempFile;
        } catch (Exception e) {
            LOGGER.error("exportCompltInfo 生成导出文件失败.", e);
        } finally {
            if (null != out) {
                try {
                    out.close();
                } catch (IOException e) {
                    LOGGER.error("exportCompltInfo close FileOutputStream error", e);
                }
            }
        }
        return null;
    }

    /**
     * 
     * 功能描述:计算各种导出数据 <br>
     * 〈功能详细描述〉
     *
     * @param taskNo
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    private void createSheetByTaskNo(HSSFWorkbook wb, String taskNo, Boolean isHq) {
        // 在Excel工作簿中建一工作表
        HSSFSheet sh = wb.createSheet("任务" + taskNo);
        // 合并单元格
        merchCell(sh);
        // 设置单元格类型
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
        cellStyle.setWrapText(true);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 创建标题
        StringBuilder titleLine0;
        if (isHq) {
            titleLine0 = new StringBuilder("大区");
        } else {
            titleLine0 = new StringBuilder("门店");
        }
        titleLine0.append(";;完成率;;;台数;;;;;;销售额;;;销售额占比;;");
        StringBuilder titleLine1;
        if (isHq) {
            titleLine1 = new StringBuilder("大区编码;大区名称");
        } else {
            titleLine1 = new StringBuilder("门店编码;门店名称");
        }
        titleLine1.append(";综合;A类;B类;目标综合;实际综合;A类目标;A类实际;B类目标;B类实际;综合;A类;B类;目标占比;实际占比");
        List<String> list = new ArrayList<String>();
        list.add(titleLine0.toString());
        list.add(titleLine1.toString());

        // 获取bi报表信息
        BIABSaleCompletion sale;
        if (isHq) {
            sale = bITaskService.queryRegions(Integer.valueOf(taskNo));
        } else {
            sale = bITaskService.queryStores(Integer.valueOf(taskNo));
        }
        // 生成总计行数据
        StringBuilder titleLine2 = assembleLine2(taskNo, isHq, sale);

        list.add(titleLine2.toString());

        // 生成各门店（大区）行数据
        assembleEveryStoreData(list, taskNo, isHq, sale);

        // 输出文件
        writeLine(sh, cellStyle, list);

    }

    /**
     * 
     * 功能描述:合并单元格 <br>
     * 〈功能详细描述〉
     *
     * @param sh
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    private void merchCell(HSSFSheet sh) {
        sh.addMergedRegion(new CellRangeAddress(0, 0, 0, 1));
        sh.addMergedRegion(new CellRangeAddress(0, 0, 2, 4));
        sh.addMergedRegion(new CellRangeAddress(0, 0, 5, 10));
        sh.addMergedRegion(new CellRangeAddress(0, 0, 11, 13));
        sh.addMergedRegion(new CellRangeAddress(0, 0, 14, 15));
    }

    /**
     * 
     * 功能描述:组装总计一行数据 <br>
     * 〈功能详细描述〉
     *
     * @param taskNo
     * @return
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    private StringBuilder assembleLine2(String taskNo, Boolean isHq, BIABSaleCompletion sale) {
        Integer destNumA = sale.getDestNumOfA() == null ? Integer.valueOf("0") : sale.getDestNumOfA();
        Integer destNumB = sale.getDestNumOfB() == null ? Integer.valueOf("0") : sale.getDestNumOfB();
        Integer compltNumA = sale.getSaleVolumeOfA() == null ? Integer.valueOf("0") : sale.getSaleVolumeOfA();
        Integer compltNumB = sale.getSaleVolumeOfB() == null ? Integer.valueOf("0") : sale.getSaleVolumeOfB();
        // A类完成比率
        BigDecimal compltRateA = (destNumA == 0) ? BigDecimal.valueOf(0)
                : BigDecimal.valueOf(compltNumA * 100.0 / destNumA).setScale(2, RoundingMode.CEILING);
        // B类完成比率
        BigDecimal compltRateB = (destNumB == 0) ? BigDecimal.valueOf(0)
                : BigDecimal.valueOf(compltNumB * 100.0 / destNumB).setScale(2, RoundingMode.CEILING);
        // 完成率综合 A类*60%+B类*40%
        BigDecimal comPltRateTotal = sale.getCompletion();
        // 目标综合
        Integer destNumTotal = sale.getDestNum() == null ? 0 : sale.getDestNum();
        // 实际综合
        Integer compltNumTotal = compltNumA + compltNumB;

        StringBuilder titleLine2 = new StringBuilder("总计;;");
        // 拼接导出总计数据行
        titleLine2.append(comPltRateTotal).append(SEMICOLON).append(compltRateA).append(SEMICOLON).append(compltRateB)
                .append(SEMICOLON).append(destNumTotal).append(SEMICOLON).append(compltNumTotal).append(SEMICOLON)
                .append(destNumA).append(SEMICOLON).append(compltNumA).append(SEMICOLON).append(destNumB)
                .append(SEMICOLON).append(compltNumB).append(SEMICOLON)
                .append(sale.getSaleAmount() == null ? 0 : sale.getSaleAmount()).append(SEMICOLON)
                .append(sale.getSaleAmountOfA() == null ? 0 : sale.getSaleAmountOfA()).append(SEMICOLON)
                .append(sale.getSaleAmountOfB() == null ? 0 : sale.getSaleAmountOfB()).append(SEMICOLON);
        if (isHq) {
            titleLine2.append(BLANK).append(SEMICOLON).append(BLANK);
        } else {
            titleLine2.append(sale.getPercent()).append(SEMICOLON).append(sale.getRate() == null ? 0 : sale.getRate());
        }
        return titleLine2;
    }

    /**
     * 
     * 功能描述:查询各个门店的数据 <br>
     * 〈功能详细描述〉
     *
     * @param list
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    private void assembleEveryStoreData(List<String> list, String taskNo, Boolean isHq, BIABSaleCompletion sale) {
        for (OrgABSaleCompletion org : sale.getOrgABSaleCompletion()) {
            handleExlLine(list, org, isHq);
        }
    }

    private void handleExlLine(List<String> list, OrgABSaleCompletion sale, Boolean isHq) {
        Integer destNumA = sale.getDestNumOfA() == null ? Integer.valueOf("0") : sale.getDestNumOfA();
        Integer destNumB = sale.getDestNumOfB() == null ? Integer.valueOf("0") : sale.getDestNumOfB();
        Integer compltNumA = sale.getSaleVolumeOfA() == null ? Integer.valueOf("0") : sale.getSaleVolumeOfA();
        Integer compltNumB = sale.getSaleVolumeOfB() == null ? Integer.valueOf("0") : sale.getSaleVolumeOfB();
        // A类完成比率
        BigDecimal compltRateA = (destNumA == 0) ? BigDecimal.valueOf(0)
                : BigDecimal.valueOf(compltNumA * 100.0 / destNumA).setScale(2, RoundingMode.CEILING);
        // B类完成比率
        BigDecimal compltRateB = (destNumB == 0) ? BigDecimal.valueOf(0)
                : BigDecimal.valueOf(compltNumB * 100.0 / destNumB).setScale(2, RoundingMode.CEILING);
        // 完成率综合 A类*60%+B类*40%
        BigDecimal comPltRateTotal = sale.getCompletion();
        // 目标综合
        Integer destNumTotal = sale.getDestNum() == null ? 0 : sale.getDestNum();
        // 实际综合
        Integer compltNumTotal = compltNumA + compltNumB;
        // 占比
        BigDecimal rate = sale.getRate();

        StringBuilder sb = new StringBuilder(sale.getOrgCode()).append(SEMICOLON).append(sale.getOrgName())
                .append(SEMICOLON).append(comPltRateTotal).append(SEMICOLON).append(compltRateA).append(SEMICOLON)
                .append(compltRateB).append(SEMICOLON).append(destNumTotal).append(SEMICOLON).append(compltNumTotal)
                .append(SEMICOLON).append(destNumA).append(SEMICOLON).append(compltNumA).append(SEMICOLON)
                .append(destNumB).append(SEMICOLON).append(compltNumB).append(SEMICOLON)
                .append(sale.getSaleAmount() == null ? "0" : sale.getSaleAmount()).append(SEMICOLON)
                .append(sale.getSaleAmountOfA() == null ? "0" : sale.getSaleAmountOfA()).append(SEMICOLON)
                .append(sale.getSaleAmountOfB() == null ? "0" : sale.getSaleAmountOfB()).append(SEMICOLON);
        if (isHq) {
            sb.append(sale.getPercent() == null ? "0" : sale.getPercent()).append(SEMICOLON)
                    .append(rate == null ? "0" : rate);
        } else {
            sb.append(BLANK).append(SEMICOLON).append(rate == null ? "0" : rate);
        }
        list.add(sb.toString());
    }

    @Override
    public List<String> queryCommClassifyListByTaskNo(String taskNo, String assignStatus, String commStatus) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("taskNo", taskNo);
        paramMap.put("assignStatus", assignStatus);
        paramMap.put("commStatus", commStatus);
        return dalClient.queryForList("mainPush.queryCommClassifyListByTaskNo", paramMap, String.class);
    }

    @Override
    public long queryCommRegionTaskUnAssignedNum(String taskNo, String assignStatus) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("taskNo", taskNo);
        paramMap.put("assignStatus", assignStatus);
        paramMap.put("commStatus", MainPushTask.CommodityStatusConstants.IS_DELETE);
        Map<String, Object> countMap = dalClient.queryForMap("mainPush.queryCommRegionTaskUnAssignedNum", paramMap);
        return (Long) countMap.get("num");
    }

    @Override
    public TaskStore queryStoreTaskInfoByParTaskNo(String taskNo, String orgCode) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("taskNo", taskNo);
        paramMap.put("orgCode", orgCode);
        List<TaskStore> list = dalClient.queryForList("mainPush.queryStoreTaskInfoByParTaskNo", paramMap,
                TaskStore.class);
        if (!CollectionUtils.isEmpty(list)) {
            return list.get(0);
        }
        return null;
    }

    @Override
    public List<String> queryCommCdsInRegion(String regionCode, String cateCode) {
        List<String> commCds = new ArrayList<String>();
        Date startTime = DateUtils.getFirstDayZeroPointOfMonth(0);
        Date endTime = DateUtils.getFirstDayZeroPointOfMonth(1);
        List<String> comms = taskStoreAndClerkDaoService.queryCommCdsInRegion(regionCode, cateCode, startTime, endTime);
        if (CollectionUtils.isNotEmpty(comms)) {
            for (String comm : comms) {
                String[] gdCds = comm.split(",");
                for (String gdCd : gdCds) {
                    commCds.add(gdCd);
                }
            }
        }
        return commCds;
    }
}
